package org.esgi.bmbdsql.builder.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.esgi.bmbdsql.builder.enumerations.SQLRequestType;
import org.esgi.bmbdsql.builder.exceptions.JoiningProblemException;
import org.esgi.bmbdsql.builder.exceptions.MissingColumnException;
import org.esgi.bmbdsql.builder.exceptions.MissingColumnValueException;
import org.esgi.bmbdsql.builder.exceptions.MissingMethodException;
import org.esgi.bmbdsql.builder.exceptions.MissingTableRootException;
import org.esgi.bmbdsql.builder.exceptions.NotUsedForThisMethodException;
import org.esgi.bmbdsql.builder.interfaces.IJoinClause;
import org.esgi.bmbdsql.builder.interfaces.IResult;
import org.esgi.bmbdsql.builder.interfaces.ISqlRequestBuilder;
import org.esgi.bmbdsql.builder.interfaces.ISqlTable;

public class SqlRequestBuilder implements ISqlRequestBuilder 
{

	
	SQLRequestType method;
	ISqlTable tableRoot;
	HashMap<ISqlTable, IJoinClause> joinTables;
	List<String> column;
	HashMap<String, String> columnValue;
	HashMap<String, Map<String,String>> whereParameters;
	Object[] resultParameters;
	
	@Override
	public IResult getResult() 
	{
		return new Result(getSQLResquest(),resultParameters);
	}
	
	String getSQLResquest()
	{
		try
		{
			if (null != method)
			{
				switch(method)
				{
					case CREATE:
						return getCreateRequest();
					case DELETE:
						return getDeleteRequest();
					case DROP:
						return getDropRequest();
					case INSERT:
						return getInsertRequest();
					case SELECT:
						return getSelectRequest();
					case UPDATE:
						return getUpdateRequest();
				}
			}
			else
				throw new MissingMethodException();
		}
		catch (JoiningProblemException e ){
			
		}
		catch (MissingTableRootException e ){
			
		}
		catch (MissingColumnValueException e ){
			
		}
		catch (MissingColumnException e ){
			
		}
		catch (MissingMethodException e ){
			
		}
		return null;
	}
	
	String getSelectRequest() throws JoiningProblemException, MissingTableRootException
	{
		String res;
		if (null!= tableRoot)
		{
			res="SELECT ";
			if (null!= column)
			{
				for(int i = 0;i<column.size();i++)
				{
					res+=column.get(i);
					if (i!= column.size()-1)
						res+=",";
				}
			}
			res+="FROM ";
			res+=tableRoot.getTableName();
			if(null != joinTables)
			{
				for(int i =0 ; i<joinTables.size();i++)
				{
					String key = (String) joinTables.keySet().toArray()[i];
					switch(joinTables.get(key).getJoinType())
					{
					case INNER:
						{
							res+="JOIN ";
							res+=key;
							res+=" ON ";
							res+=joinTables.get(key).getJoinClause();
							break;
						}
					case LEFT_OUTER:
						{
							res+="LEFT OUTER JOIN ";
							res+=key;
							res+=" ON ";
							res+=joinTables.get(key).getJoinClause();
							break;
						}
					default:
						throw new JoiningProblemException();
					}	
				}
			}
			if(null != whereParameters)
			{
				res+=" WHERE ";
				for(int i = 0;i<whereParameters.size();i++)
				{
					String key = (String) whereParameters.keySet().toArray()[i];
					res+=key;
					String value = (String) whereParameters.get(key).keySet().toArray()[0];
					res+=whereParameters.get(key).get(value);
					res+= value;
					if (i!= whereParameters.size()-1)
						res+=" AND ";
				}
			}
			res+=";";
		}
		else
			throw new MissingTableRootException();
		return res;
	}
	
	String getInsertRequest() throws MissingColumnValueException, MissingColumnException, MissingTableRootException
	{
		String res;
		if (null!= tableRoot)
		{
			if ( null!= column)
			{
				if (null != columnValue)
				{
					res="INSERT (";
					for(int i = 0;i<column.size();i++)
					{
						res+=column.get(i);
						if (i!= column.size()-1)
							res+=",";
					}
					res+=") INTO ";
					res+=tableRoot.getTableName();
					res+="VALUES (";
					for(int i = 0;i<column.size();i++)
					{
						res+=columnValue.get(column.get(i));
						if (i!= column.size()-1)
							res+=",";
					}
					res+=")";
					res+=";";
				}
				else
					throw new MissingColumnValueException();	
			}
			else
				throw new MissingColumnException();
		}
		else
			throw new MissingTableRootException();
		return res;
	}
	
	String getCreateRequest() throws MissingColumnException, MissingTableRootException
	{
		String res;
		if (null!= tableRoot)
		{
			if ( null!= column)
			{
				res="CREATE TABLE ";
				res+=tableRoot.getTableName();
				res+="(";
				for(int i = 0;i<column.size();i++)
				{
					res+=column.get(i);
					if (i!= column.size()-1)
						res+=",";
				}
				res+=")";
				res+=";";
			}
			else
				throw new MissingColumnException();		
		}
		else
			throw new MissingTableRootException();
		return res;
	}
	
	String getDropRequest() throws MissingTableRootException
	{
		String res;
		if (null!= tableRoot)
		{
			res="DROP TABLE ";
			res+=tableRoot.getTableName();
			res+=";";
		}
		else
			throw new MissingTableRootException();
		return res;
	}
	
	String getUpdateRequest() throws MissingTableRootException, MissingColumnValueException, MissingColumnException
	{
		String res;
		if (null!= tableRoot)
		{
			if (null!= column)
			{
				if (null != columnValue)
				{
					res="UPDATE TABLE ";
					res+=tableRoot.getTableName();
					res+=" SET ";
					for(int i = 0;i<column.size();i++)
					{
						res+=column.get(i);
						res +=" = ";
						res+=columnValue.get(column.get(i));
						if (i!= column.size()-1)
							res+=" AND ";
					}
					res+=" WHERE ";
					for(int i = 0;i<whereParameters.size();i++)
					{
						String key = (String) whereParameters.keySet().toArray()[i];
						res+=key;
						String value = (String) whereParameters.get(key).keySet().toArray()[0];
						res+=whereParameters.get(key).get(value);
						res+= value;
						if (i!= whereParameters.size()-1)
							res+=" AND ";
					}
					res+=";";
				}
				else
					throw new MissingColumnValueException();	
			}
			else
				throw new MissingColumnException();
		}
		else
			throw new MissingTableRootException();
		return res;
	}
	
	String getDeleteRequest() throws MissingTableRootException
	{
		String res;
		if (null!= tableRoot)
		{
			res="DELETE FROM ";
			res+=tableRoot.getTableName();
			if (null != whereParameters)
			{
				res+=" WHERE ";
				for(int i = 0;i<whereParameters.size();i++)
				{
					String key = (String) whereParameters.keySet().toArray()[i];
					res+=key;
					String value = (String) whereParameters.get(key).keySet().toArray()[0];
					res+=whereParameters.get(key).get(value);
					res+= value;
					if (i!= whereParameters.size()-1)
						res+=" AND ";
				}
			}
			res+=";";
		}
		else
			throw new MissingTableRootException();
		return res;
	}
	
	@Override
	public void setMethod(SQLRequestType method) {
		this.method  = method;
		this.tableRoot = null;
		this.joinTables = null;
		this.column = null;
		this.columnValue = null;
		this.whereParameters = null;
		this.resultParameters = null;
		
	}

	@Override
	public void setTableRoot(ISqlTable tableRoot) {
		this.tableRoot = tableRoot;	
	}

	@Override
	public void addColumn(String columnName) throws NotUsedForThisMethodException {
		if (this.method == SQLRequestType.DROP 
				|| this.method == SQLRequestType.DELETE)
		{
			throw new NotUsedForThisMethodException();
		}
		else
		{
			if( null == this.column)
			{
				this.column = new ArrayList<String>(); 
			}
			this.column.add(columnName);
		}
	}

	@Override
	public void addColumnValue(String ColumnName, String value) throws NotUsedForThisMethodException {
		if (this.method == SQLRequestType.DROP 
				|| this.method == SQLRequestType.DELETE 
				|| this.method == SQLRequestType.CREATE)
		{
			throw new NotUsedForThisMethodException();
		}
		else
		{
			if( null == this.columnValue)
			{
				this.columnValue = new HashMap<String,String>(); 
			}
			this.columnValue.put(ColumnName, value);
		}
	}

	@Override
	public void addWhereParameter(String ColumnName, String Value, String type) throws NotUsedForThisMethodException {
		if (this.method == SQLRequestType.DROP 
				|| this.method == SQLRequestType.CREATE 
				|| this.method == SQLRequestType.INSERT)
		{
			throw new NotUsedForThisMethodException();
		}
		else
		{
			if( null == this.whereParameters)
			{
				this.whereParameters = new HashMap<String,Map<String,String>>(); 
			}
			HashMap<String,String> tab = new HashMap<>();
			tab.put(Value, type);
			this.whereParameters.put(ColumnName,tab);
		}
		
	}

	@Override
	public void setResultParameters(Object[] resultParameters) throws NotUsedForThisMethodException {
		if (this.method == SQLRequestType.DROP 
				|| this.method == SQLRequestType.INSERT 
				|| this.method == SQLRequestType.CREATE)
		{
			throw new NotUsedForThisMethodException();
		}
		else
		{
			this.resultParameters = resultParameters;
		}
		
	}
	
	@Override
	public void addTable(ISqlTable table, IJoinClause joinClause) throws NotUsedForThisMethodException {
		if (this.method == SQLRequestType.DROP 
				|| this.method == SQLRequestType.INSERT 
				|| this.method == SQLRequestType.CREATE  
				|| this.method == SQLRequestType.DELETE  
				|| this.method == SQLRequestType.UPDATE)
		{
			throw new NotUsedForThisMethodException();
		}
		else
		{
			if( null == this.joinTables)
			{
				this.joinTables = new HashMap<ISqlTable,IJoinClause>(); 
			}
			this.joinTables.put(table,joinClause);
		}
	}

}
